Excel Rehberi
Kara Geτi■ Noktas²n² Excel ▌le Hesaplama
Excel 95'in i■letmelerdeki kullan²m²na ili■kin ÷rneklerimiz, bu ay ba■aba■ noktas² analizleri ile devam ediyor.Bu ay i■letmeleri yak²ndan ilgilendiren bir konunun ExcelÆdeki uygulamalar²na de≡iniyoruz. Ba■aba■ noktas² analizi, kara geτi■ analizi, orjinal ad² ile "Break-Even Analysis" ■eklinde τok τe■itli adlar² olan bu tekni≡in τok geni■ bir kullan²m alan² vard²r. K²saca Maliyet-▌■ Hacmi-Kar Analizi olan bu tekni≡i i■letmelerde Finans, Muhasebe ÷zellikle Maliyet Muhasebesi ve ▄retim b÷lⁿmlerinde τal²■anlar etkin bir ■ekilde kullanmaktad²r.
Ba■aba■ noktas² analizi, esas itibar²yla, sabit giderler, de≡i■ken giderler ve kar aras²ndaki ili■kilerin incelenmesinde kullan²lan bir analiz tekni≡idir. Ba■aba■ noktas² analizi, bir kontrol, bir y÷netim arac² olarak;
* Firman²n, zarardan kaτ²nabilmesi iτin ula■mas² gerekli i■ hacmi dⁿzeyinin saptanmas²,
* ╟e■itli ⁿretim dⁿzeylerinde birim maliyetlerinin hesaplanmas²,
* De≡i■ken giderler, sabit giderler ve birim sat²■ fiyat²ndaki de≡i■ikliklerin, firman²n kara geτi■ noktas² ve toplam kar²na olabilecek etkilerin incelenmesi.
* Asgari sat²■ fiyat²n²n belirlenmesi,
* Kar hedeflerine ula■²lmas² iτin gerekli i■ hacminin saptanmas² ve burada yazamad²≡²m daha pek τok karar durumunda yayg²n olarak kullan²l²r.
Ba■aba■ noktas² analizi yap²l²rken, di≡er bir deyi■le i■letme iτin kara geτi■, kara ba■lama veya kar s²f²r noktas² hesaplan²rken pek τok analiz tekni≡inde oldu≡u gibi bir tak²m varsay²mlar yap²lmaktad²r.
1) ▌■letmenin giderleri, sabit ve de≡i■ken giderler olarak ikiye ayr²lmaktad²r.
2) Sabit giderler τe■itli ⁿretim seviyelerinde ayn² kalmaktad²r.
3) De≡i■ken giderler, ⁿretim miktar² ile ayn² oranda de≡i■mekte, azalmakta veya artmaktad²r. Di≡er bir deyi■le, birim de≡i■ken giderler, tⁿm ⁿretim seviyelerinde sabit kalmaktad²r.
4) Birim sat²■ fiyat² de≡i■memektedir.
5) Sat²■larla ⁿretim aras²nda tam bir zaman uyumu vard²r, ÷nemli stok de≡i■meleri olmamaktad²r.
6) ▌■letme tek bir mal veya hizmet ⁿretmektedir. ▌■letme birden fazla mal veya hizmet ⁿretiyorsa, ⁿretimin bile■imi de≡i■memektedir.
Ba■aba■ Noktas² Hesaplanmas²nda Kullan²lan Formⁿller
Yukar²da aτ²klanan varsay²mlar alt²nda, bir firman²n de≡i■ken giderlerinin, ⁿretim miktarlar²n²n ve sat²■ has²lat²n²n da, sat²■ miktar²n²n do≡rusal fonksiyonlar² oldu≡unu kabul etmek, kara geτi■ ba■aba■ noktas²n²n hesaplanmas²n² kolayla■t²rmaktad²r. Ba■aba■ noktas², toplam gelirlerin toplam maliyetlere e■it oldu≡u sat²■ hacmi (miktar (ton)) veya tutar (TL) olarak tan²mlan²r. Bu durum a■a≡²daki denklemlerle ifade edilir:F + VQ = PQ (Ba■aba■ Analizi Genel Denklemi) (1)
F = Sabit Giderler (Fixed Expenses)
V = Birim Ba■²na De≡i■ken Gider (Variable Expense)
Q = Kara Geτi■ Noktas²na Ula■abilmesi ▌τin ▄retilmesi ve
Sat²lmas² Gereken Miktar (Quantity)
P = Birim Sat²■ Fiyat² (Sat²■lar=Revenue)
P-V = Birim Ba■²na Katk² Pay²
(P-V)/P = Birim Ba■²na Katk² Oran² (Contribution Margin)Ba■aba■ Sat²■ Miktar² = Toplam Sabit Giderler/Birim Ba■²na Katk² Pay² (2)
Ba■aba■ Sat²■ Tutar² (TL) = Toplam Sabit Giderler/Birim Ba■²na Katk² Oran² (3)
Birim Katk² Oran² = 1 - (Birim De≡i■ken Gider/Birim Sat²■ Fiyat²) (4)
Bir i■letmenin, ba■aba■ noktas² yukar²daki denklem ve formⁿller yard²m²yla hesaplanmaktad²r. Buradaki Katk² Oran², sat²■ hacmindeki de≡i■melerin kar ⁿzerindeki etkisini sⁿratle hesaplayabilme olana≡² sa≡lar. ╓rne≡in, bir i■letmenin katk² oran², %25 olsun. ▌■letme 1,800,000 TLÆlik sat²■ art²■² ile kar■²la■²rsa bu i■letmenin kar²nda 1800000 TL * %25 =450000 TL art²■ sa≡lan²r. Bu aτ²klamalar²n ²■²≡²nda ba■aba■ analizi ile ilgili iki ÷rnek problemi Excel yard²m²yla τ÷zelim.
ExcelÆde Tek Tip ▄rⁿn ▌τin Kara Geτi■ Analizi
╓RNEK I: ▄retim kapasitesi y²lda 34 bin ton, birim sat²■ fiyat² 30,000 TL , birim ba■²na de≡i■ken gideri 10,000 TL ve toplam sabit gideri 500,000 TL olan bir i■letmenin, ba■aba■ noktas²ndaki de≡erleri Excel'de bir tablo dⁿzenleyerek g÷sterelim. Rakamlar semboliktir.
Excel ekran²ndaki tabloya g÷re, firma ayn² ⁿrⁿnden 25 ton ⁿretti≡inde ve bu miktar² satt²≡²nda kara geτi■ noktas²na gelmektedir. 25 ton ⁿrⁿn satt²≡²nda zarar etme halinden kurtulmaktad²r. 25 tonluk sat²■ miktar² zarar ve kar durumu iτin bir s²n²r de≡erdir. Firman²n kara geτmesi iτin 25 ton ⁿrⁿnⁿ satmas², 750.000 TL sat²■ tutar² elde etmesi gerekmektedir.
Kara geτme noktas² olan ba■aba■ noktas²ndaki de≡erler, (2), (3), (4) nolu denklem yard²m²yla a■a≡²daki gibi hesaplanmaktad²r.
Katk² Oran² = (30,000-10,000)/30,000 @ 0.67 Ba■aba■ Sat²■ Tutar² = 500,000/0.67 @ 750,000 TL Ba■aba■ Sat²■ Miktar² = 500,000/(30,000-10,000) @ 25 tonKara geτi■ noktas² analizi ile ilgili olarak tek ⁿrⁿn ⁿreten bir firman²n kara geτmek iτin ne miktar ⁿretim yapmas² gerekti≡ini ExcelÆde yap²lan hesaplamalarla ba■ka bir ÷rnek ⁿzerinde τ÷zⁿmleyelim. ╓rnek IÆin τal²■ma sayfas²ndan farkl² olarak ╓rnek IIÆdeki Sabit Giderler, Katk² Oran², Gelirdeki Art²■ Oran² sabit de≡erler ile verilmi■tir. Kara geτme noktas² olarak ifade edilen ba■aba■ noktas² iτin yap²lan hesaplamalarda ╓rnek IÆden farkl² olarak kullan²lan formⁿller kutu iτinde oklar ile g÷sterilmi■tir.
╓RNEK II: Firman²n sabit giderleri $280,000, birim ba■²na katk² oran² %40 oldu≡u bir i■letmede, kara geτme noktas²ndaki di≡er bir deyi■le firman²n kar ve zarar²n²n ba■aba■ noktas²ndaki sat²lacak ⁿrⁿn miktar², bu sat²■tan elde edilecek gelir ve toplam de≡i■ken gider hesaplanmak istenmektedir.
Firman²n kara geτmesi iτin ⁿretti≡i mal veya hizmetten $700,000 gelir elde etmesi gerekmektedir. Kara geτi■ noktas²ndaki gelir, sabit giderlerin, katk² oran²na b÷lⁿnmesi ile bulunur. De≡i■ken giderler ise gelirin, de≡i■ken gider oran² (1-Katk² Oran²) ile τarp²lmas² ile bulunur. Toplam giderler ise sabit giderler ile de≡i■ken giderlerin toplanmas² ile ve en son olarak kar gelirden toplam giderlerin τ²kar²lmas² ile elde edilir.
B sⁿtununa bak²l²rsa gelirdeki art²■ oran²n²n $20,000 oldu≡u g÷rⁿlⁿr. De≡i■ik ⁿretim seviyeleri incelenirse kara 11 ton veya birim ⁿretim yapt²ktan sonra geτilmektedir. ▐imdi τizilen kara geτi■ grafi≡i ⁿzerinde bir tak²m de≡erleri de≡i■tirerek kara geτi■ noktas²ndaki de≡erler iτin hedef arama (Goal Seek) yapal²m.
Hedef Ara (Goal Seek) ╓zelli≡i
Kara geτi■ grafi≡inin nas²l τizildi≡ini k²saca maddeler halinde ÷zetledikten sonra grafik ⁿzerinde hedef arama ÷zelli≡ini kullanal²m. Kara Geτi■ Grafi≡inin ╟izimi:1) Grafi≡in τizilece≡i say²lar²n bulundu≡u B37:F50 alan² fare ile τal²■ma sayfas² ⁿzerinde belirlenir. Bu alan², klavye ile belirlemek iτin B37 hⁿcresine gidilir ve F8 tu■una bas²l²r. S²rayla sa≡ ve alt ok tu■lar² ile hareket edilerek alan belirlenir - yani seτili hale getirilir - ve Enter tu■una bas²l²r.
2) Standart isimli araτ τubu≡u ⁿzerinde grafi≡in τal²■ma sayfas² ⁿzerindeki konumunu belirlemek ⁿzere araτ τubu≡undaki Chart Wizard (Grafk Sihirbaz²) simgesi t²klan²r. ╟al²■ma sayfas² ⁿzerinde art² g÷rⁿntⁿsⁿnde olan farenin sol tu■u bas²l² tutularak τizilmek istenen grafi≡in boyutlar²na g÷re bo■ bir alan belirlenir.
3) Ekrana gelen Chart Wizart Step 1 of 5 (Grafik Sihirbaz² - Ad²m 1/5) diyalog kutusunda say²lar²n bulundu≡u alan²n adresi yaz²l² oldu≡u iτin Next (ileri) dⁿ≡mesi t²klan²r.
4) Grafi≡i τizgi grafik olarak τizmek iτin Chart Wizart Step 2 of 5 (Grafik Sihirbaz² - Ad²m 2/5) diyalog kutusunda Line (╟izgi) grafik seτene≡i fare ile t²klan²r. Ard²ndan Next (▌leri) dⁿ≡mesi t²klan²r.
5) ╟izgi grafi≡in tipini belirlemek iτin Chart Wizart Step 3 of 5 (Grafik Sihirbaz² - Ad²m 3/5) diyalog kutusunda 2 veya 10 seτene≡i t²klan²r. Ard²ndan Next (▌leri) dⁿ≡mesi t²klan²r.
6) Chart Wizart Step 4 of 5 (Grafik Sihirbaz² - Ad²m 4/5) diyalog kutusunda, Use First Column (▌lk ... Sⁿtun) ve Use First Row (▌lk ... Sat²r) seτeneklerinin ok dⁿ≡meleri t²klanarak herhangi bir say² de≡i■ikli≡i yap²lmaz ve 0 olarak b²rak²l²r. Next dⁿ≡mesi t²klan²r.
7) Chart Wizart Step 5 of 5 (Grafik Sihirbaz² - Ad²m 5/5) diyalog kutusunda, grafi≡in X ekseninin ve Y ekseninin isimleri belirlenen kutulara yaz²l²r ve Finish (Son) dⁿ≡mesi t²klan²r.
Kara Geτi■ Grafi≡indeki Do≡rular²n²n ▌simlendirilmesi:
Kara geτi■ grafi≡i, ekranda belirlenen alana τizilir. ╟al²■ma sayfas² ⁿzerinde bulunan grafik τift t²klan²rsa grafik etkin hale getirilir. Grafik ⁿzerindeki herhangi bir do≡ru (De≡i■ken Giderler Do≡rusu) ⁿzerine fare ile bir kez t²klan²r. Etkin hale gelen do≡runun ⁿzerinde siyah renkte karecikler g÷rⁿnⁿr. Bu kez do≡ru, fare ile sa≡ t²klan²r ekrana gelen k²sayol menⁿsⁿnden Format Data Series (Veri Serilerini Biτimle) komutu seτilir. Ekrana bir diyalog kutusu gelir. Burada Name and Values (Ad ve De≡erler) b÷lmesi fare ile t²klan²r. Name (Ad) isimli metin kutusuna De≡i■ken Giderler yaz²l²r ve OK (Tamam) dⁿ≡mesi fare ile t²klan²r. Bu isimlendirme i■lemi grafikteki di≡er do≡rular iτin tekrarlan²rsa do≡rular²n isimleri grafik ⁿzerinde istenen yere yaz²l²r.
Grafik ▄zerinde Hedef Arama (Goal Seek)
Kara geτi■ noktas²nda gelirin 1000 TL civar²na yⁿkseldi≡i varsay²lmaktad²r. Grafik ⁿzerinde sat²■ gelirleri do≡rusunun kara geτi■ noktas²ndaki bu art²■²n, kara geτi■ noktas²ndaki di≡er de≡erleri (ⁿrⁿn miktar²n², sabit ve de≡i■ken giderlerini) nas²l de≡i■tirece≡i incelenmek istenmektedir. Bu olay²n etkisini grafik ⁿzerine g÷rebilmek iτin a■a≡²daki i■lemler yap²lmal²d²r.
╟al²■ma sayfas² ⁿzerinde bulunan kara geτi■ grafi≡i τift t²klan²rsa, grafik etkin hale getirilir. Grafik ⁿzerindeki Sat²■ Geliri do≡rusu ⁿzerinde kara geτi■ noktas² fare ile bir kez sol t²klan²r. Do≡runun karecikler ile kapland²≡² g÷rⁿlⁿr. Ard²ndan tekrar kara geτi■ noktas² fare ile bir kez sol t²klan²r ve sadece kara geτi■ noktas²nda karecikler g÷rⁿnⁿr. Bu noktada fare imleci iki tarafl² ok halini al²r. Fare bu haldeyken sol fare tu■una bas²l²p yukar²ya, 1000 TL de≡erine do≡ru τekilirse kesikli bir τizgi yⁿkselir. Burada farenin sol tu■u serbest b²rak²l²rsa ekrana Goal Seek (Hedef Ara) diyalog kutusu gelir.
F15 hⁿcresinde bulunan gelir de≡eri 1000 TL olursa B15 hⁿcresinde sat²lan ⁿrⁿn ne olaca≡² tan²mlamak iτin Goal Seek diyalog kutusunda gerekli tan²mlamalar yap²l²r. OK dⁿ≡mesi t²klan²r. Gelir 1000 TL oldu≡u zaman ⁿretim miktar² 33 ton, de≡i■ken giderler 333 TL olmakta, sabit giderler de≡i■memektedir. Bu durumda 167 TL kar elde edilmektedir. Bu rakam herhalde liradan s²f²rlar at²l²nca anlam kazanacakt²r.
Birden Fazla Mamul ▄retimi
Firmalar genellikle birden fazla mal ve hizmet ⁿretmektedirler. Bu nedenle tek mamul ⁿretimini anlatan yukar²daki ÷rneklerin uygulama alan²n² geni■letmek gerekmektedir. Birden fazla mal ⁿreten firmalar²n kara geτi■ noktas²n²n hesaplanabilmesi iτin, (1) her bir mamulⁿn genel sat²■ hacmi iτindeki pay²n²n ve (2) her mamulⁿn birim katk² pay²n²n ve katk² oran²n²n bulunmas² gerekir.
╓RNEK III: Ekranda d÷rt ayr² mamul iτin bir Ba■aba■ Noktas² Analiz Tablosu dⁿzenlenmi■tir. Her mamul τe■idinin birim sat²■ fiyat², birim de≡i■ken maliyeti, toplam sat²■lar iτindeki pay² s²rayla B, C, D sⁿtunlar² boyunca verilmi■tir. Her mamul iτin kara geτi■ noktas²n²n hesaplanmas² iτin katk² pay² ve katk² oran² E ve F sⁿtunlar² boyunca hesapland²ktan sonra G ve H sⁿtunlar²nda kara geτi■ noktas²ndaki ⁿrⁿn miktar² ve sat²■ tutar² bulunur.
Firman²n imalat, sat²■ ve genel y÷netim giderleri, sabit giderler olarak 1,000,000 TL tutar²ndad²r. D÷rt ⁿrⁿn iτin karar geτi■ noktas²ndaki toplam sat²lan ⁿrⁿn miktar²n² ve sat²■ tutar²n² bulabilmek iτin E11 hⁿcresindeki toplam katk² oran² hesaplanmal²d²r.
Her mal²n sat²■ iτindeki yⁿzdesi ve katk² pay² oran² farkl² oldu≡undan E11 hⁿcresinde =SUMPRODUCT (=TOPLA.╟ARPIM) fonksiyonunu kullanarak, tart²l² ortalama almak yolu ile, toplam katk² oran² hesaplanmal², di≡er bir deyi■le her bir mala ait sat²■ yⁿzdeleri (D3:D6) ile birim katk² oranlar² (F3:F6) τarp²larak toplanmal²d²r. Kara geτi■ noktas² olan ba■aba■ noktas²nda toplam de≡i■ken maliyeti, E12 hⁿcresinde =SUMPRODUCT(C3:C6;G3:G6) ((=TOPLA.╟ARPIM(C3:C6;G3:G6)) formⁿlⁿ ile hesaplan²r. E13 hⁿcresinde, G3:G6 alan²nda d÷rt ⁿrⁿnⁿn ayr² ayr² kara geτi■ noktas²nda hesaplanm²■ olan miktarlar² =SUM(G3:G6) (=TOPLA(G3:G6)) formⁿlⁿ ile toplanarak kara geτi■ noktas²ndaki toplam ⁿrⁿn miktar² bulunur. Benzer ■ekilde, E14 hⁿcresinde, H3:H6 alan²nda d÷rt ⁿrⁿnⁿn hesaplanm²■ olan sat²■ tutarlar² =SUM(H3:H6) (=TOPLA(H3:H6)) formⁿlⁿ ile kara geτi■ noktas²ndaki toplam sat²■ miktar² hesaplan²r.
Bir ba■ka yolla, =SUMPRODUCT(B3:B6;G3:G6) (=TOPLA.╟ARPIM(B3:B6;G3:G6)) formⁿlⁿ ile kara geτi■ noktas²ndaki toplam sat²■ tutar² hesaplan²r. E15 hⁿcresinde, toplam sat²■ tutar²ndan sabit ve de≡i■ken giderler τ²kar²larak =SUMPRODUCT(B3:B6;G3:G6)-E10-E12 (=TOPLA.╟ARPIM(B3:B6;G3:G6)-E10-E12) formⁿlⁿ ile kar/zarar hesab² yap²l²r. E15 hⁿcresinde ba■aba■ noktas²nda, kar s²f²r olarak bulunur.